package com.gym.model.dao;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.gym.model.entity.Course;
import com.gym.model.entity.Trainer;
import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;

public class DAOCourse extends DAO{
	
	public DAOCourse() throws ConnessioneException {
		super();
	}
	
	
	public int insert(Course c) throws ViolazioneIntegritaException, MetodoDaoException{
		
		String query="INSERT INTO course(activity, note, idtrainer, maxcustomers, lifespan, price)"
					+"            values(?       , ?   , ?        , ?           , ?       , ?    )";
		
		try{
			pst = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
			//sostituisce i marcatori ?:
			pst.setString(1, c.getActivity());
			pst.setString(2, c.getNote());
			pst.setObject(3, c.getIdtrainer(), java.sql.Types.INTEGER);
			pst.setShort(4, c.getMaxcustomers());
			pst.setShort(5, c.getLifespan());
			pst.setDouble(6, c.getPrice());
			
			pst.executeUpdate(); //esegue la query
			
			//ottiene il ResultSet con una sola tupla col valore di PK generata
			res = pst.getGeneratedKeys();
			
			res.next();	//si posiziona sulla tupla del ResultSet
			
			return res.getInt(1); //prende la prima colonna del ResultSet: PK generato
		
		} catch(MySQLIntegrityConstraintViolationException e){
			
			if(e.getErrorCode()==1452)
				throw new ViolazioneIntegritaException("ERRORE INSERT COURSE: FK fuori dominio "+e.getMessage());
			else
				throw new ViolazioneIntegritaException("ERRORE INSERT COURSE: altra violazione R.I. "+e.getMessage()+
													   " - ErrorCode="+e.getErrorCode());
		} catch(SQLException e){
			throw new MetodoDaoException("ERRORE INSERT COURSE: "+e.getMessage()+
										 " - ErrorCode="+e.getErrorCode()+"  |  QUERY: "+pst);
		}finally{
			try {pst.close();} catch(SQLException e){}
		}
	}
	
	public boolean search (int idcourse) throws MetodoDaoException, DatoNonTrovatoException {

		try {
			pst = con.prepareStatement
							("SELECT *"
							+"FROM payment "
							+"WHERE idcourse = ? ");

			pst.setInt(1, idcourse);
			res = pst.executeQuery();

			if(res.next()) 
				return true;
			else
				return false;

		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE SELECT COURSE con argomento id: "+e.getMessage()+"\nErrorCode="+e.getErrorCode());
		} finally {
			try {pst.close();} catch (SQLException e) {	}
		}
	}
	
	
	public ArrayList<Course> select() throws DatoNonTrovatoException, MetodoDaoException {
		
		ArrayList<Course> al = new ArrayList<>();
		try {
			stm = con.createStatement();
			res = stm.executeQuery
			("SELECT c.id, c.activity, c.note, c.idtrainer, c.maxcustomers, c.lifespan, c.price "
			+"     , t.id, t.trainerfirstname, t.trainerlastname, t.trainerphone, t.traineremail, t.trainerskill, t.trainernote "
			+"FROM course c LEFT JOIN trainer t  ON c.idtrainer = t.id "
			+"ORDER BY c.activity ");
			
			while(res.next()) { //cicla il ResultSet e costruisce oggetto Corso
				al.add( componiCourse() ); //aggiungo l'oggetto Corso in lista
 			}
			if(al.size()==0)
				throw new DatoNonTrovatoException("Attenzione tabella COURSE vuota!");
			else
				return al;
			
		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE SELECT COURSE: "+e.getMessage()
										+" - ErrorCode="+e.getErrorCode());
		} finally {
			try {stm.close();} catch(SQLException e){}
		}
	}

	
	/**
	 * metodo per estrarre il Course corrispondente alla PK passata in argomento
	 * @param id valore intero della PK
	 * @return Course bean della tabella COURSE
	 * @throws MetodoDaoException
	 * @throws DatoNonTrovatoException
	 */
	public Course select(int id) throws MetodoDaoException, DatoNonTrovatoException {

		try {
			pst = con.prepareStatement
			("SELECT c.id, c.activity, c.note, c.idtrainer, c.maxcustomers, c.lifespan, c.price "
			+"     , t.id, t.trainerfirstname, t.trainerlastname, t.trainerphone, t.traineremail, t.trainerskill, t.trainernote "
			+"FROM course c LEFT JOIN trainer t  ON c.idtrainer = t.id "
			+"WHERE c.id = ? ");
			
			pst.setInt(1, id);
			
			res = pst.executeQuery(); //esegue la query!
		
			if(res.next()) 
				return componiCourse(); 
			else
				throw new DatoNonTrovatoException("Nessuna tupla trovata in COURSE per PK id="+id);
			
		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE SELECT COURSE con argomento id: "+e.getMessage()
										+" - ErrorCode="+e.getErrorCode());
		} finally {
			try {pst.close();} catch(SQLException e){}
		}
	}
	
	
	public Course delete(int id) throws MetodoDaoException, DatoNonTrovatoException {
		
		try {
			Course course = select(id);  //salvo preventivamente il Course da cancellare
			
			pst = con.prepareStatement("DELETE FROM course WHERE id = ?");
			pst.setInt(1, id); 
			
			pst.executeUpdate(); //esegue la query di DELETE
	
			return course;
			
		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE DELETE COURSE per PK id="+id
										+"  |  message: "+e.getMessage()+" - ErrorCode="+e.getErrorCode());
		} finally {
			try {pst.close();} catch(SQLException e){}
		}
	}
	
	
	public Course delete(Course c) throws MetodoDaoException, DatoNonTrovatoException {
		return delete(c.getId());
	}

	
	public Course update(Course cnew) throws MetodoDaoException, DatoNonTrovatoException {

		try {
			Course cold = select(cnew.getId()); //prelevo il Course prima di aggiornarlo!
			if( cnew.getIdtrainer()==-1)
				cnew.setIdtrainer(null);
			pst = con.prepareStatement
			("UPDATE course "
			+"SET activity=?, note=?, idtrainer=?, maxcustomers=?, lifespan=?, price=? "
			+"WHERE id = ? ");
			//sostituisce i marcatori ?:
			pst.setString(1, cnew.getActivity());
			pst.setString(2, cnew.getNote());
			pst.setObject(3, cnew.getIdtrainer(), java.sql.Types.INTEGER);
			pst.setShort(4, cnew.getMaxcustomers());
			pst.setShort(5, cnew.getLifespan());
			pst.setDouble(6, cnew.getPrice());
			pst.setInt(7, cnew.getId());
			
			pst.executeUpdate(); //esegue la query di UPDATE
	
			return cold;
			
		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE UPDATE TRAINER: "+e.getMessage()
										+" - ErrorCode="+e.getErrorCode()+"  |  QUERY: "+pst);
		} finally {
			try {pst.close();} catch(SQLException e){}
		}
	}

	
	//METODI DI SERVIZIO: tutti private!
	
	private Course componiCourse() throws SQLException{
		
		return new Course(res.getInt(1)
						, res.getString(2)
						, res.getString(3)
						, res.getString(4)==null ? null : res.getInt(4)
						, res.getShort(5)
						, res.getShort(6)
						, res.getDouble(7)
						
						, componiTrainer() );
	}
	
	private Trainer componiTrainer() throws SQLException{

		return new Trainer (res.getInt(8)
						  , res.getString(9)
						  , res.getString(10)
						  , res.getString(11)
						  , res.getString(12)
						  , res.getString(13)
						  , res.getString(14) );		
	}
		
	
	//METODO main() per TESTARE il DAO
		
	public static void main(String[] args) {
		
		//NB: serve necessariamente inserire uno User in quanto il Customer ha una FK sulla tabella User
		
		Course cnew = new Course(0, "NUOVO", "NOTE", 1, (short)50, (short)60, 25.50);

		try {
			DAOCourse dao = new DAOCourse();
			
			//eseguire i metodi in 'logica' cascata:
			
			int pk = dao.insert(cnew);
			System.out.println("\ninsert()........: "+pk);
			
			System.out.println("\nselect()........: ");
			for(Course c : dao.select()) System.out.println(c);
			
			System.out.println("\nselect(pk)......: "+dao.select(pk));
			
			Course cupd = new Course(pk, "NUOVO UPDATE", "NOTE UPDATE", 1, (short)350, (short)360, 35.39);
			System.out.println("\nupdate(cupd)....: "+dao.update(cupd));

			System.out.println("\ndelete(pk)......: "+dao.delete(pk));

		} catch (ConnessioneException | MetodoDaoException | DatoNonTrovatoException | ViolazioneIntegritaException e) {
			e.printStackTrace();
		}

	}

	
	public ArrayList<Course> selectToTrainer(int idTrainer) 
		throws MetodoDaoException, DatoNonTrovatoException {

		ArrayList<Course> al = new ArrayList<>();
		try {
			pst = con.prepareStatement
			("SELECT c.id, c.activity, c.note, c.idtrainer, c.maxcustomers, c.lifespan, c.price "
			+"     , t.id, t.trainerfirstname, t.trainerlastname, t.trainerphone, t.traineremail, t.trainerskill, t.trainernote "
			+"FROM course c LEFT JOIN trainer t  ON c.idtrainer = t.id "
			+"WHERE t.id = ? ");
			
			pst.setInt(1, idTrainer);
			
			res = pst.executeQuery(); //esegue la query!
		
			while(res.next()) { //cicla il ResultSet e costruisce oggetto Corso
				al.add( componiCourse() ); //aggiungo l'oggetto Corso in lista
 			}
			if(al.size()==0)
				throw new DatoNonTrovatoException("Attenzione tabella vuota!");
			else
				return al;
			
		} catch (SQLException e) {
			throw new MetodoDaoException("ERRORE SELECT COURSE con argomento idTrainer: "+e.getMessage()
										+" - ErrorCode="+e.getErrorCode());
		} finally {
			try {pst.close();} catch(SQLException e){}
		}
	}	
		
}